package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by pjn on 2017/8/14.
 */
public class RoadLineRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<DTLineFaultDto> getRoadLineFaultReport4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId, String roadName, String startRoad, String endRoad) throws Exception {
        String sql = "SELECT rl.RoadLineId,rl.RoadLinePhoto,rl.RoadLineProvince,rl.RoadLineCity,rl.RoadLineArea," +
                "rl.RoadLineRegionId,o.OrgName AS roadLineOrgName,u.UserName AS roadLineCreatorName," +
                "rl.RoadLineInstallTime\n" +
                "FROM ((tb_road_line rl  JOIN tb_org o ON o.OrgId=rl.RoadLineOrgId)\n" +
                "JOIN tb_user u ON u.UserId=rl.RoadLineCreatorId)\n" +
                "WHERE rl.RoadLineRoadName LIKE '%" + roadName + "%' AND" +
                " rl.RoadLineStartRoad LIKE '%" + startRoad + "%' AND rl.RoadLineEndRoad LIKE '%" + endRoad + "%'";
        if (actorId != 1) {
            sql += " and rl.RoadLineOrgId=" + orgId + "";
        }
        if (startRoad != "" || endRoad != "") {
            sql += " UNION SELECT rl.RoadLineId,rl.RoadLinePhoto,rl.RoadLineProvince,rl.RoadLineCity,rl.RoadLineArea," +
                    "rl.RoadLineRegionId,o.OrgName AS roadLineOrgName,u.UserName AS roadLineCreatorName," +
                    "rl.RoadLineInstallTime\n" +
                    "FROM ((tb_road_line rl  JOIN tb_org o ON o.OrgId=rl.RoadLineOrgId)\n" +
                    "JOIN tb_user u ON u.UserId=rl.RoadLineCreatorId)\n" +
                    "WHERE rl.RoadLineRoadName LIKE '%" + roadName + "%' AND" +
                    " rl.RoadLineStartRoad LIKE '%" + endRoad + "%' AND rl.RoadLineEndRoad LIKE '%" + startRoad + "%'";
        }
        if (actorId != 1) {
            sql += " and rl.RoadLineOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTLineFaultDto.class));
        List<DTLineFaultDto> lineFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return lineFaultDtos;
    }

    public List<String> getRoadURoadName(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT rl.RoadLineStartRoad AS roadLineStartRoad\n" +
                "FROM tb_road_line rl\n" +
                "WHERE rl.RoadLineRoadName LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and rl.RoadLineMainOrgId=" + orgId + " ";
            else
                sql += " and rl.RoadLineOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT rl.RoadLineEndRoad AS roadLineStartRoad\n" +
                "FROM tb_road_line rl\n" +
                "WHERE rl.RoadLineRoadName LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and rl.RoadLineMainOrgId=" + orgId + " ";
            else
                sql += " and rl.RoadLineOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getSectionUStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT rl.RoadLineStartRoad AS roadLineStartRoad\n" +
                "FROM tb_road_line rl\n" +
                "WHERE rl.RoadLineEndRoad LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and rl.RoadLineMainOrgId=" + orgId + " ";
            else
                sql += " and rl.RoadLineOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT rl.RoadLineEndRoad AS roadLineStartRoad\n" +
                "FROM tb_road_line rl\n" +
                "WHERE rl.RoadLineStartRoad LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and rl.RoadLineMainOrgId=" + orgId + " ";
            else
                sql += " and rl.RoadLineOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<RoadLineDto> getRoadLine4DT(DTRequestDto dtRequestDto,
                                            String actorIds,
                                            Integer orgId,
                                            Boolean audit,
                                            String startRoad,
                                            String endRoad,
                                            String roadName) throws Exception {
        String sql = "select se.*, ur.UserName as roadLineCreatorName, " +
                "og1.OrgName as roadLineOrgName, og2.OrgName as roadLineMainOrgName " +
                "from tb_road_line se, tb_user ur, tb_org og1, tb_org og2 " +
                "where ur.UserId = se.RoadLineCreatorId " +
                "and og1.OrgId = se.RoadLineOrgId and og2.OrgId = se.RoadLineMainOrgId and " +
                "se.RoadLineRoadName like '%" + roadName + "%' and se.RoadLineStartRoad like '%" + startRoad + "%' " +
                "and se.RoadLineEndRoad like '%" + endRoad + "%' and se.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql += "and roadLineIsComplete = 0 ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and se.RoadLineMainOrgId=" + orgId + " ";
        else
            sql += "and se.RoadLineOrgId = " + orgId + " ";
        if (startRoad != "" || endRoad != "") {
            sql += "union select se.*, ur.UserName as roadLineCreatorName, " +
                    "og1.OrgName as roadLineOrgName, og2.OrgName as roadLineMainOrgName " +
                    "from tb_road_line se, tb_user ur, tb_org og1, tb_org og2 " +
                    "where ur.UserId = se.RoadLineCreatorId " +
                    "and og1.OrgId = se.RoadLineOrgId and og2.OrgId = se.RoadLineMainOrgId and " +
                    "se.RoadLineRoadName like '%" + roadName + "%' and se.RoadLineStartRoad like '%" + endRoad + "%' " +
                    "and se.RoadLineEndRoad like '%" + startRoad + "%' and se.ProjectNumber like '%" + dtRequestDto.getSearch() + "%'";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and se.RoadLineMainOrgId=" + orgId + " ";
            else
                sql += "and se.RoadLineOrgId = " + orgId + " ";
        }
        sql += "order by RoadLineInstallTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadLineDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getRoadLine4DTCount(DTRequestDto dtRequestDto,
                                       String actorIds,
                                       Integer orgId,
                                       Boolean audit,
                                       String startRoad,
                                       String endRoad,
                                       String roadName) throws Exception {
        String sql1 = "select count(*) " +
                "from tb_road_line se, tb_user ur, tb_org og1, tb_org og2 " +
                "where ur.UserId = se.RoadLineCreatorId " +
                "and og1.OrgId = se.RoadLineOrgId and og2.OrgId = se.RoadLineMainOrgId and " +
                "se.RoadLineRoadName like '%" + roadName + "%' and se.RoadLineStartRoad like '%" + startRoad + "%' " +
                "and se.RoadLineEndRoad like '%" + endRoad + "%' and se.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql1 += "and roadLineIsComplete = 0 ";
        if (actorIds.indexOf('1') >= 0)
            sql1 += "";
        else if (actorIds.indexOf('7') >= 0)
            sql1 += "and se.RoadLineMainOrgId=" + orgId + " ";
        else
            sql1 += "and se.RoadLineOrgId = " + orgId + " ";

        Query query1 = em.createNativeQuery(sql1);
        Integer count1 = Integer.parseInt(query1.getSingleResult().toString());
        Integer count2 = 0;
        if (startRoad != "" || endRoad != "") {
            String sql2 = "select count(*) " +
                    "from tb_road_line se, tb_user ur, tb_org og1, tb_org og2 " +
                    "where ur.UserId = se.RoadLineCreatorId " +
                    "and og1.OrgId = se.RoadLineOrgId and og2.OrgId = se.RoadLineMainOrgId and " +
                    "se.RoadLineRoadName like '%" + roadName + "%' and se.RoadLineStartRoad like '%" + endRoad + "%' " +
                    "and se.RoadLineEndRoad like '%" + startRoad + "%' and se.ProjectNumber like '%" + dtRequestDto.getSearch() + "%'";
            if (actorIds.indexOf('1') >= 0)
                sql2 += "";
            else if (actorIds.indexOf('7') >= 0)
                sql2 += "and se.RoadLineMainOrgId=" + orgId + " ";
            else
                sql2 += "and se.RoadLineOrgId = " + orgId + " ";
            Query query2 = em.createNativeQuery(sql2);
            count2 = Integer.parseInt(query2.getSingleResult().toString());
        }


        return count2 + count1;
    }

    public List<String> getStartRoadByRoadName(String roadName, String actorIds, Integer orgId) throws Exception {
        String sql = "select  RoadLineStartRoad as roadName from tb_road_line where RoadLineRoadName like '%" + roadName + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and roadLineMainOrgId=" + orgId + " ";
        else
            sql += "and roadLineOrgId = " + orgId + " ";
        sql += "union select RoadLineEndRoad as roadName from tb_road_line where RoadLineRoadName like '%" + roadName + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and roadLineMainOrgId=" + orgId + " ";
        else
            sql += "and roadLineOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getEndRoadByStartRoad(String roadLineRoadName, String startRoad, String actorIds, Integer orgId) throws Exception {
        String sql = "select RoadLineStartRoad as roadName from tb_road_line where RoadLineRoadName like '%" + roadLineRoadName + "%'" +
                " and  RoadLineEndRoad like '%" + startRoad + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and roadLineMainOrgId=" + orgId + " ";
        else
            sql += "and roadLineOrgId = " + orgId + " ";
        sql += "union select RoadLineEndRoad as roadName from tb_road_line where RoadLineRoadName like '%" + roadLineRoadName + "%'" +
                " and  RoadLineStartRoad like '%" + startRoad + "%'  ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and roadLineMainOrgId=" + orgId + " ";
        else
            sql += "and roadLineOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<RoadLineDto> getRoadLineCoordinates4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception {
        String sql = "SELECT CoordinateTypeId as elementId , " +
                "GROUP_CONCAT(CoordinateLongitude order by CoordinateOrder)as longitude, " +
                "GROUP_CONCAT(CoordinateLatitude order by CoordinateOrder)as latitude " +
                "from tb_coordinate  where CoordinateType = 1 ";
        if (actorId != 1) {
            if (orgType == 0)
                sql += "and CoordinateTypeId in(SELECT RoadLineId FROM tb_road_line where roadLineOrgId=" + orgId + ") ";
            else if (orgType == 1)
                sql += "and CoordinateTypeId in(SELECT RoadLineId FROM tb_road_line where roadLineMainOrgId=" + orgId + ") ";
        }
        sql += "  group by CoordinateTypeId ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadLineDto.class));
        return query.getResultList();
    }

    public List<LineFaultDto> getRoadLineCoordinates4AppMap(Integer orgId, Integer orgType, Integer actorId) throws Exception {
        String sql = "SELECT CoordinateTypeId as elementId, " +
                "GROUP_CONCAT(CoordinateLongitude order by CoordinateOrder) as longitude, " +
                "GROUP_CONCAT(CoordinateLatitude order by CoordinateOrder) as latitude, " +
                "RoadLineId, RoadLinePhoto " +
                "from tb_coordinate c,tb_road_line l " +
                "where c.CoordinateTypeId = l.RoadLineId AND CoordinateType = 1 ";
        if (actorId != 1) {
            if (orgType == 0)
                sql += "and CoordinateTypeId in(SELECT RoadLineId FROM tb_road_line where roadLineOrgId=" + orgId + ") ";
            else if (orgType == 1)
                sql += "and CoordinateTypeId in(SELECT RoadLineId FROM tb_road_line where roadLineMainOrgId=" + orgId + ") ";
        }
        sql += "  group by CoordinateTypeId  ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(LineFaultDto.class));
        return query.getResultList();
    }

    public List<String> getCity4RoadLine(String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "SELECT DISTINCT RoadLineCity from tb_road_line ";
        if (actorIds.indexOf('1') >= 0) ;
        else if (actorIds.indexOf('7') >= 0)
            sql += " where RoadLineMainOrgId=" + orgId + " ";
        else
            sql += " where RoadLineOrgId=" + orgId + "";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getStartRoadByCity(String city, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "SELECT sm.RoadLineStartRoad AS roadLineStartRoad " +
                "FROM tb_road_line sm where sm.roadLineCity like '%" + city + "%'";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and sm.RoadLineMainOrgId=" + orgId + " ";
        else
            sql += " and sm.RoadLineOrgId=" + orgId + " ";
        sql += " UNION SELECT sm.RoadLineEndRoad AS roadLineStartRoad " +
                "FROM tb_road_line sm where sm.roadLineCity like '%" + city + "%'";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and sm.RoadLineMainOrgId=" + orgId + " ";
        else
            sql += " and sm.RoadLineOrgId=" + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getSectionByStartRoad4App(String city, String roadName, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "SELECT sm.RoadLineStartRoad AS roadLineStartRoad FROM tb_road_line sm " +
                "WHERE sm.RoadLineEndRoad LIKE '%" + roadName + "%' and sm.RoadLineCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and sm.RoadLineMainOrgId=" + orgId + " ";
        else
            sql += " and sm.RoadLineOrgId=" + orgId + " ";

        sql += " UNION SELECT sm.RoadLineEndRoad AS roadLineStartRoad FROM tb_road_line sm " +
                "WHERE sm.RoadLineStartRoad LIKE '%" + roadName + "%' and sm.RoadLineCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and sm.RoadLineMainOrgId=" + orgId + " ";
        else
            sql += " and sm.RoadLineOrgId=" + orgId + " ";

        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }


    public List<RoadLineDto> getRoadLine4APP(String actorIds, Integer orgId, Integer mCount, Integer mSize, String city, String startRoad, String endRoad, String roadName) throws Exception {
        String sql = "select m.*, ur.UserName as roadLineCreatorName, " +
                "og1.OrgName as roadLineOrgName, og2.OrgName as roadLineMainOrgName " +
                "from tb_road_line m, tb_user ur, tb_org og1, tb_org og2 " +
                "where ur.UserId = m.RoadLineCreatorId " +
                "and og1.OrgId = m.RoadLineOrgId and og2.OrgId = m.RoadLineMainOrgId " +
                "and m.RoadLineStartRoad like '%" + startRoad + "%' and m.RoadLineEndRoad like '%" + endRoad + "%' " +
                "and m.RoadLineRoadName like '%" + roadName + "%' and m.RoadLineCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and m.RoadLineMainOrgId=" + orgId + " ";
        else
            sql += "and m.RoadLineOrgId = " + orgId + " ";
        if (startRoad != "" || endRoad != "") {
            sql += "union select m.*, ur.UserName as roadLineCreatorName," +
                    "og1.OrgName as roadLineOrgName, og2.OrgName as roadLineMainOrgName " +
                    "from tb_road_line m, tb_user ur, tb_org og1, tb_org og2 " +
                    "where ur.UserId = m.RoadLineCreatorId " +
                    "and og1.OrgId = m.RoadLineOrgId and og2.OrgId = m.RoadLineMainOrgId " +
                    "and m.RoadLineStartRoad like '%" + endRoad + "%' and m.RoadLineEndRoad like '%" + startRoad + "%' " +
                    "and m.RoadLineRoadName like '%" + roadName + "%' and m.RoadLineCity like '%" + city + "%' ";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and m.RoadLineMainOrgId=" + orgId + " ";
            else
                sql += "and m.RoadLineOrgId = " + orgId + " ";
        }
        sql += "order by concat(RoadLineInstallTime, roadLineId) desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadLineDto.class));
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<RoadLineDto> getRoadLineById(Integer roadLineId) throws Exception {
        String sql = "select se.* ,o1.orgName as roadLineOrgName,o2.OrgName as roadLineMainOrgName," +
                " u.userName as roadLineCreatorName from tb_road_line se,tb_org o1,tb_user u,tb_org o2 " +
                "where o1.orgId=se.roadLineOrgId and o2.orgId=se.roadLineMainOrgId and u.UserId=se.roadLineCreatorId" +
                " and se.roadLineId=" + roadLineId + " ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadLineDto.class));
        return query.getResultList();
    }

    public List<String> getRoadName4RoadLineByCity(String actorIds, Integer orgId, String city, Integer mCount, Integer mSize) throws Exception {
        String sql = "select DISTINCT RoadLineRoadName from  tb_road_line where RoadLineCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and roadLineMainOrgId=" + orgId + " ";
        else
            sql += " and roadLineOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getEndRoadByStartRoad4App(String city, String roadName, String startRoad, String actorIds, Integer orgId, Integer mCount, Integer mSize) {
        String sql = "select RoadLineStartRoad as roadName from tb_road_line where RoadLineRoadName like '%" + roadName + "%'" +
                " and  RoadLineEndRoad like '%" + startRoad + "%' and RoadLineCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and roadLineMainOrgId=" + orgId + " ";
        else
            sql += "and roadLineOrgId = " + orgId + " ";
        sql += "union select RoadLineEndRoad as roadName from tb_road_line where RoadLineRoadName like '%" + roadName + "%'" +
                " and  RoadLineStartRoad like '%" + startRoad + "%' and RoadLineCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and roadLineMainOrgId=" + orgId + " ";
        else
            sql += "and roadLineOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getStartRoadByRoadName4App(String city, String roadName, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "select  RoadLineStartRoad as roadName from tb_road_line where RoadLineRoadName like '%" + roadName + "%' " +
                "and RoadLineCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and roadLineMainOrgId=" + orgId + " ";
        else
            sql += "and roadLineOrgId = " + orgId + " ";
        sql += "union select RoadLineEndRoad as roadName from tb_road_line where RoadLineRoadName like '%" + roadName + "%' " +
                "and RoadLineCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and roadLineMainOrgId=" + orgId + " ";
        else
            sql += "and roadLineOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<RoadLineDto> getRoadLine4Map4App(Integer orgId,
                                                 String actorIds,
                                                 double neLongitude,
                                                 double neLatitude,
                                                 double swLongitude,
                                                 double swLatitude) throws Exception {
        String sql = "select * from tb_road_line ";
        sql += "where roadLineId in ( " +
                "select CoordinateTypeId from tb_coordinate where CoordinateType = 1 " +
                "and CoordinateLongitude < :neLongitude and CoordinateLongitude > :swLongitude " +
                "and CoordinateLatitude < :neLatitude and CoordinateLatitude > :swLatitude " +
                "group by CoordinateType, CoordinateTypeId)";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and roadLineMainOrgId = " + orgId + " ";
        else
            sql += "and roadLineOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        query.setParameter("neLongitude", String.valueOf(neLongitude));
        query.setParameter("swLongitude", String.valueOf(swLongitude));
        query.setParameter("neLatitude", String.valueOf(neLatitude));
        query.setParameter("swLatitude", String.valueOf(swLatitude));
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadLineDto.class));
        return query.getResultList();
    }
}
